import duckdb, io
import pandas as pd, os
from utils.path_utils import get_project_root
from database.duckdb.duckdb_base import get_connection



def get_pagedata(p_dwbh, p_ledger, p_model, p_qzq_type, search, p_page = 1, p_pageSize = 10 ):

    con = get_connection()
    # 获取查询参数
    if search is not None:
        params = {key: value for key, value in search.items()}

    # 基础查询
    query = f"SELECT * FROM V_TEMP_ALL_GSCLOUD t WHERE dwbh = '{p_dwbh}' and ledger = '{p_ledger}' "

    # 根据传入的查询参数构建 SQL 条件
    conditions = []
    if search is not None:
        for key, value in params.items():
            if value:
                if key == 'je':
                    conditions.append(f"je = {value}")
                elif key == 'je_begin':
                    conditions.append(f"je >= {value}")
                elif key == 'je_end':
                    conditions.append(f"je <= {value}")
                elif key == 'xmbh':
                    conditions.append(f"zx02bh like '{value}%'")
                elif key == 'fybh':
                    conditions.append(f""" 
        exists (
          select 1 from NW_FYMC_MX fy
           where fy.dwbh = t.dwbh
             and (fy.kjnd is null or fy.kjnd = t.year) -- 年度
             and t.kmbh like fy.kmbh||'%'  -- 科目
             and (fy.jsfs = 'JFFS' and t.jzfx = 1 or fy.jsfs = 'DFFS' and t.jzfx = 1)  -- 记账方向 
             and fy.fybh = '{value}'
             and fy.model = '{p_model}'
             and fy.status = '1'
        )
                    """)
                elif key == 'code':
                    conditions.append(f""" 
        exists (
          select 1 from v_nw_project_hsm hsm
           where hsm.dwbh = t.dwbh
             and (hsm.kjnd is null or hsm.kjnd = t.year) -- 年度
             and t.zx02bh like hsm.hsm_xmbh||'%'  -- 项目 
             and hsm.code = '{value}'
             and hsm.model = '{p_model}'
             and ( qzq_type is null or '{p_qzq_type}' is null or qzq_type = '{p_qzq_type}' )  -- 收支余类型
        )
                    """)
                else:
                    conditions.append(f"{key} LIKE '%{value}%'")

    # 如果有条件，加入 WHERE 子句
    if conditions:
        query += " AND " + " AND ".join(conditions)
    print(query)
    # 获取数据总条数
    total_count = con.execute(f"SELECT COUNT(*) FROM ({query}) ").fetchone()[0]

    # 计算分页查询的起始位置
    l_offset = (p_page - 1) * p_pageSize
    query += f" ORDER BY year, kjqj, pzbh, jzfx, je LIMIT {p_pageSize} OFFSET {l_offset}"
    # 执行查询
    result = con.execute(query).fetchall()

    # 获取表头（列名）
    columns = [col[0] for col in con.description]

    # 将结果转换为字典列表（键值对）
    data = [dict(zip(columns, row)) for row in result]

    # result = con.execute("SELECT * FROM V_TEMP_ALL_GSCLOUD "
    #                      " WHERE dwbh = $p_dwbh and ledger = $p_ledger "
    #                      "   and ( year = $l_kjnd or $l_kjnd is null) "
    #                      " ORDER BY year, kjqj, pzbh, jzfx, je"
    #                      " LIMIT $p_pageSize OFFSET $p_offset ",
    #                      {'p_dwbh':p_dwbh, 'p_ledger':p_ledger, 'l_kjnd':l_kjnd, 'p_pageSize':p_pageSize, 'p_offset':l_offset}).fetchall()
    #
    # # 获取表头（列名）
    # columns = [col[0] for col in con.description]
    #
    # # 将结果转换为字典列表（键值对）
    # data = [dict(zip(columns, row)) for row in result]
    #
    # # 获取数据总条数
    # total_count = con.execute("SELECT COUNT(*) FROM V_TEMP_ALL_GSCLOUD "
    #                      " WHERE dwbh = $p_dwbh and ledger = $p_ledger "
    #                      "   and ( year = $p_kjnd or $p_kjnd is null) ",
    #                           {'p_dwbh':p_dwbh, 'p_ledger':p_ledger, 'p_kjnd':p_kjnd}).fetchone()[0]

    con.close()
    return [data, total_count]

def get_pzk(p_dwbh, p_ledger, p_kjnd, p_kjqj, p_pzbh ):

    con = get_connection()

    query = """
        select id, year as kjnd, accperiodcode as kjqj, accdoccode as pzbh, accdocdate as pzrq, 
                cast(numberofattch as integer) as pjzs, '记账凭证' pzlx, 
                accmanagername as kjzg, makername as zdr,
                bookername as jzr, approvername as shr, cashiername as cn
          from T_API_PZ
         where dwbh = $p_dwbh
           and ledger = $p_ledger
           and year = $p_kjnd
           and accperiodcode = $p_kjqj
           and accdoccode = $p_pzbh
    """
    # 执行查询
    result = con.execute(query, {'p_dwbh': p_dwbh, 'p_ledger': p_ledger, 'p_kjnd': p_kjnd, 'p_kjqj': p_kjqj, 'p_pzbh': p_pzbh}).fetchall()


    # 获取表头（列名）
    columns = [col[0] for col in con.description]

    # 将结果转换为字典列表（键值对）
    data = [dict(zip(columns, row)) for row in result]


    con.close()
    return data


def get_pzfl(p_dwbh, p_ledger, p_kjnd, p_pzid, p_chartofacc ):
    con = get_connection()

    query = """
            select s.accdocid as pzid, s.id as flid, s.abstract as zy, km.code as kmbh, km.name_chs as kmmc, 
                    km.fullname_chs as kmqc,  
                   case when s.LENDINGDIRECTION = '1' then round(s.amount, 2) else 0 end jfje,
                   case when s.LENDINGDIRECTION = '2' then round(s.amount, 2) else 0 end dfje,
                   cast(s.LENDINGDIRECTION as integer) jzfx
              from (select * from T_API_PZFL
             where dwbh = $p_dwbh
               and ledger = $p_ledger
               and year = $p_kjnd
               and accdocid = $p_pzid) s
              left join (select km.id, km.code, km.name_chs, km.fullname_chs, km.fiyear
                           from t_api_km km
                          where (km.chartofacc = $p_chartofacc /*or km.chartofaccbh = '01'*/)) km
                on s.ACCTITLECODE = km.code -- s.acctitleid = km.id
               and s.year = km.fiyear
             order by s.accentrycode, s.LENDINGDIRECTION, km.code, s.amount, s.id
        """
    # 执行查询
    result = con.execute(query, {'p_dwbh': p_dwbh, 'p_ledger': p_ledger, 'p_kjnd': p_kjnd, 'p_pzid': p_pzid, 'p_chartofacc': p_chartofacc}).fetchall()

    # 获取表头（列名）
    columns = [col[0] for col in con.description]

    # 将结果转换为字典列表（键值对）
    data = [dict(zip(columns, row)) for row in result]

    con.close()
    return data

def get_fzys(p_dwbh, p_ledger, p_kjnd, p_pzid, p_flid, search, p_page = 1, p_pageSize = 10 ):
    con = get_connection()
    # 获取查询参数
    if search is not None:
        params = {key: value for key, value in search.items()}

    query = """
                SELECT * 
                  FROM V_TEMP_ALL_GSCLOUD t
                 where dwbh = $p_dwbh
                   and ledger = $p_ledger
                   and year = $p_kjnd
                   and pzid = $p_pzid
                   and flid = $p_flid
            """

    # 根据传入的查询参数构建 SQL 条件
    conditions = []
    if search is not None:
        for key, value in params.items():
            if value:
                if key == 'je':
                    conditions.append(f"je = {value}")
                elif key == 'je_begin':
                    conditions.append(f"je >= {value}")
                elif key == 'je_end':
                    conditions.append(f"je <= {value}")
                elif key == 'xmbh':
                    conditions.append(f"zx02bh like '{value}%'")
                elif key == 'xmmc':
                    conditions.append(f"zx02mc like '%{value}%'")
                elif key == 'xmqc':
                    conditions.append(f"zx02qc like '%{value}%'")
                elif key == 'wldwbh':
                    conditions.append(f"wldwbh like '%{value}%'")
                elif key == 'wldwmc':
                    conditions.append(f"wldwmc like '%{value}%'")
                elif key == 'all':
                    conditions.append(f"(zy like '%{value}%' or wldwbh like '%{value}%' or wldwmc like '%{value}%' "
                                      f" or zx02bh like '%{value}%' or zx02qc like '%{value}%' or cast(je as text) like '%{value}%' )")
                else:
                    conditions.append(f"{key} LIKE '%{value}%'")

    # 如果有条件，加入 WHERE 子句
    if conditions:
        query += " AND " + " AND ".join(conditions)
    print(query)
    # 获取数据总条数
    total_count = con.execute(f"SELECT COUNT(*) FROM ({query}) ", {'p_dwbh': p_dwbh, 'p_ledger': p_ledger, 'p_kjnd': p_kjnd, 'p_pzid': p_pzid,
                                 'p_flid': p_flid}).fetchone()[0]

    # 计算分页查询的起始位置
    l_offset = (p_page - 1) * p_pageSize
    query += f" ORDER BY fzbh, wldwmc, zx02qc, je, fzid LIMIT {p_pageSize} OFFSET {l_offset}"

    # 执行查询
    result = con.execute(query, {'p_dwbh': p_dwbh, 'p_ledger': p_ledger, 'p_kjnd': p_kjnd, 'p_pzid': p_pzid,
                                 'p_flid': p_flid}).fetchall()

    # 获取表头（列名）
    columns = [col[0] for col in con.description]

    # 将结果转换为字典列表（键值对）
    data = [dict(zip(columns, row)) for row in result]

    con.close()
    return [data, total_count]